package zk2

import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession

object Test {
  def main(args: Array[String]): Unit = {
    val conf = new SparkConf().setAppName("app").setMaster("local[*]")
    val session = SparkSession.builder.config(conf).enableHiveSupport.getOrCreate
    import session.implicits._

    val value = session.sparkContext.textFile("src/main/java/zk2/新建文本文档.txt")

    value.map(x=>{
      val strings = x.split("\t")
      strings match {
        case Array(q,w,e,r,t,y)=>(q,w,e,r,t,y)
      }
    }).toDF("orderid"	,"userip",	"items",	"goodsname"	,"price",	"pcs").createOrReplaceTempView("seleinfo")


    //查询所有数据
    session.sql("select * from seleinfo").show()
//    -- 使用sqarksql查询每个店铺的商品的最高价格，打印到控制台（/**/
    session.sql(
      """
        |
        |select items,max(price) from seleinfo group by items;
        |""".stripMargin).show()
//    --     使用sqarksql查询高于化妆品总消费金额的商品类别
    session.sql(
      """
        |
        |with t1 as (  select items,sum(price)s from seleinfo where items="化妆品" group by items),
        |     t2 as (select items,sum(price)o from seleinfo group by items )
        |select t2.items from t1
        |left join t2 where t2.o>t1.s;
        |""".stripMargin).show()

//    -- 使用sqarksql查询标题中家具销售金额超过1000的数据，打印到控制台
    session.sql(
      """
        |select * from seleinfo where items="家具" and price>1000;
        |
        |""".stripMargin).show()


  }
}
